﻿USE DS2012
GO

IF OBJECT_ID('usp_Dislocations_get') IS NOT NULL
	DROP PROCEDURE usp_Dislocations_get
GO

CREATE PROCEDURE usp_Dislocations_get 
	@fltOperation int = -1 --varchar(max) = NULL
	,@fltCarNumber varchar(8) = NULL
	,@fltStationOfOperation int = -1 --varchar(max) = NULL
	,@fltDepartureStation int = -1 --varchar(max) = NULL
	,@fltArrivalStation int = -1 --varchar(max) = NULL

AS SET NOCOUNT ON
BEGIN
	SELECT loc.ID
			,loc.CarNumber
	--		,loc.RollCheck
	--		,loc.Lightweight
	--		,loc.KindOfCar
	--		,ct.Code/*Name*/ AS TypeOfConditionalOfCar
			,loc.StartDate
	--		,loc.FeatTracking
	--		,loc.StateCode
	--		,loc.ActualCargoWeight
			,ASt.Code/*Name*/ AS ArrivalStation
	--		,CR.Code/*Name*/ AS CargoRate
	--		,CO.Code/*Name*/ AS CargoOperative
	--		,rec.Code/*Name*/ AS Recipient
	--		,loc.SpecialNotesOfCar
	--		,loc.NumberOfCaseCG
	--		,loc.NumberOfCaseCP
	--		,loc.NumberOfCaseKG
	--		,loc.NumberOfCaseKP
	--		,loc.ListNote
	--		,loc.CarParkType
	--		,loc.FaultCode
			,DSt.Code/*Name*/ AS DepartureStation
			,op.Code/*Name*/ AS Operation
			,loc.OperationDate
			,OSt.Code/*Name*/ AS StationOfOperation
	--		,DR.Code/*Name*/ AS DeliveryRoad
	--		,RR.Code/*Name*/ AS ReceptRoad
			,loc.TrainIndex
			,loc.TrainNumber 
	FROM Dislocation loc
	LEFT JOIN CarTypes ct ON ct.ID = loc.TypeOfConditionalCodeOfCarID
	LEFT JOIN Stations ASt ON ASt.ID = loc.ArrivalStationID
	LEFT JOIN Stations DSt ON DSt.ID = loc.DepartureStationID
	LEFT JOIN Stations OSt ON OSt.ID = loc.StationOfOperationID
	LEFT JOIN Operations op ON op.ID = loc.OperationID
--	LEFT JOIN Roads DR ON DR.ID = loc.DeliveryRoadID
--	LEFT JOIN Roads RR ON RR.ID = loc.ReceptRoadID
--	LEFT JOIN Cargo CR ON CR.ID = loc.CargoRateID
--	LEFT JOIN Cargo CO ON CO.ID = loc.CargoOperativeID
--	LEFT JOIN Recipients rec ON rec.ID = loc.RecipientID
	WHERE (DSt.Code/*Name*/ = @fltDepartureStation OR @fltDepartureStation = -1/*IS NULL*/) 
		AND (OSt.Code/*Name*/ = @fltStationOfOperation OR @fltStationOfOperation = -1/*IS NULL*/)
		AND (ASt.Code/*Name*/ = @fltArrivalStation OR @fltArrivalStation = -1/*IS NULL*/)
		AND (op.Code/*Name*/ = @fltOperation OR @fltOperation = -1/*IS NULL*/)
		AND (CarNumber = @fltCarNumber OR @fltCarNumber IS NULL)



END